import sqlite3
import os

# 创建示例数据库
def create_example_database():
    db_path = "example_ecommerce.db"
    
    # 如果数据库已存在，删除它
    if os.path.exists(db_path):
        os.remove(db_path)
    
    conn = sqlite3.connect(db_path)
    cursor = conn.cursor()
    
    # 用户表
    cursor.execute('''
        CREATE TABLE users (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            username TEXT NOT NULL UNIQUE,
            email TEXT NOT NULL UNIQUE,
            password TEXT NOT NULL,
            full_name TEXT,
            phone TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )
    ''')
    
    # 商品分类表
    cursor.execute('''
        CREATE TABLE categories (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            parent_id INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (parent_id) REFERENCES categories(id)
        )
    ''')
    
    # 商品表
    cursor.execute('''
        CREATE TABLE products (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            name TEXT NOT NULL,
            description TEXT,
            price REAL NOT NULL,
            stock_quantity INTEGER DEFAULT 0,
            category_id INTEGER,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (category_id) REFERENCES categories(id)
        )
    ''')
    
    # 订单表
    cursor.execute('''
        CREATE TABLE orders (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            order_number TEXT NOT NULL UNIQUE,
            total_amount REAL NOT NULL,
            status TEXT DEFAULT 'pending',
            shipping_address TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
    ''')
    
    # 订单明细表
    cursor.execute('''
        CREATE TABLE order_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            order_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            unit_price REAL NOT NULL,
            subtotal REAL NOT NULL,
            FOREIGN KEY (order_id) REFERENCES orders(id),
            FOREIGN KEY (product_id) REFERENCES products(id)
        )
    ''')
    
    # 购物车表
    cursor.execute('''
        CREATE TABLE cart_items (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            product_id INTEGER NOT NULL,
            quantity INTEGER NOT NULL,
            added_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id),
            FOREIGN KEY (product_id) REFERENCES products(id),
            UNIQUE(user_id, product_id)
        )
    ''')
    
    # 用户地址表
    cursor.execute('''
        CREATE TABLE user_addresses (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            user_id INTEGER NOT NULL,
            address_line1 TEXT NOT NULL,
            address_line2 TEXT,
            city TEXT NOT NULL,
            state TEXT,
            postal_code TEXT,
            country TEXT NOT NULL,
            is_default BOOLEAN DEFAULT FALSE,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (user_id) REFERENCES users(id)
        )
    ''')
    
    # 商品评论表
    cursor.execute('''
        CREATE TABLE product_reviews (
            id INTEGER PRIMARY KEY AUTOINCREMENT,
            product_id INTEGER NOT NULL,
            user_id INTEGER NOT NULL,
            rating INTEGER NOT NULL CHECK (rating >= 1 AND rating <= 5),
            comment TEXT,
            created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
            FOREIGN KEY (product_id) REFERENCES products(id),
            FOREIGN KEY (user_id) REFERENCES users(id),
            UNIQUE(user_id, product_id)
        )
    ''')
    
    # 插入示例数据
    cursor.execute("INSERT INTO users (username, email, password, full_name, phone) VALUES ('admin', 'admin@example.com', 'admin123', '管理员', '13800138000')")
    cursor.execute("INSERT INTO users (username, email, password, full_name, phone) VALUES ('user1', 'user1@example.com', 'user123', '张三', '13900139000')")
    
    cursor.execute("INSERT INTO categories (name, description) VALUES ('电子产品', '各种电子设备')")
    cursor.execute("INSERT INTO categories (name, description, parent_id) VALUES ('手机', '智能手机', 1)")
    cursor.execute("INSERT INTO categories (name, description, parent_id) VALUES ('电脑', '台式机和笔记本', 1)")
    
    cursor.execute("INSERT INTO products (name, description, price, stock_quantity, category_id) VALUES ('iPhone 15', '最新款苹果手机', 6999.00, 100, 2)")
    cursor.execute("INSERT INTO products (name, description, price, stock_quantity, category_id) VALUES ('MacBook Pro', '苹果笔记本电脑', 12999.00, 50, 3)")
    
    conn.commit()
    conn.close()
    
    print(f"示例数据库已创建: {db_path}")
    print("数据库包含以下表：")
    print("- users (用户表)")
    print("- categories (商品分类表)")
    print("- products (商品表)")
    print("- orders (订单表)")
    print("- order_items (订单明细表)")
    print("- cart_items (购物车表)")
    print("- user_addresses (用户地址表)")
    print("- product_reviews (商品评论表)")
    print("\n这些表之间有完整的外键关系，适合演示ER图生成功能。")

if __name__ == "__main__":
    create_example_database()